<?php
set_time_limit(0);
/** 
 * @author Administrator
 * 
 */
class Reparation extends CI_Model {
	
	private $report = array();
	
	private $updateTimeLimit;
	private $logDir;
	
	/**
	 */
	function __construct() {
		parent::__construct();
		$this->load->database();
		$this->updateTimeLimit = strtotime('2017-03-17 00:00:00');
		$this->apiOutput->debug = TRUE;
		
		User::$_db = &$this->db;
		
		$this->report['users'] = &User::$users;
		$this->report['sql']   = &$this->db->queries;
		
		
		$this->logDir = APPPATH.'logs/Reparation/';
		if( ! is_dir($this->logDir) ){
			mkdir($this->logDir,0777);
		}
		
	}
	public function backup(){
		$data = array();
		$oldDB = 'heshui';
		$bakDB = "hs_bak_".date('Y_m_d_H_i_s');
		$sql = "CREATE DATABASE {$bakDB}";
		$res = $this->db->query($sql);
		$data[] = array($res,$this->db->last_query());
		
		$tables = array('hs_user');
		for ($i=0;$i<10;$i++){
			$tables[] = 'hs_order_'.$i;
		}
		
		foreach ($tables as $table){
			$bak = $bakDB.'.'.$table;
			$old = $oldDB.'.'.$table;
			
			// 创建表
			$sql = "CREATE TABLE {$bak} SELECT * FROM {$old} WHERE 1=2";
			$res = $this->db->query($sql);
			$data[] = array($res,$this->db->last_query());
			// 复制数据
			$sql = "INSERT INTO {$bak} (SELECT * FROM {$old}) ";
			$res = $this->db->query($sql);
			$data[] = array($res,$this->db->last_query());
		}
		var_dump($data);
	}
	
	
	
	
	
	//赔偿给重复计费的用户
	// 将审查为重复的订单 update amount=0
	// 重新计算累计消费
	// 赔偿金额 = 累计充值+累计赠送-累计消费
	// 将赔偿金额赠送给用户
	// mongo订单表:amount=0的不缓存
	public function run(){
		$this->backup();
		// 更新重复订单amount=0
		for ($i=0;$i<10;$i++){
			User::$users =array();
			$table = "hs_order_{$i}";
			$this->check($table);
			User::generate();
			$this->resetCache($table);
			
			
			$report = json_encode($this->report,JSON_UNESCAPED_UNICODE);
// 			echo $report;die;
			file_put_contents($this->logDir.$table.'.json',$report);
		}
		
		echo json_encode($this->db->queries);
	}
	
	public function resetCache($table){
		// 清除mongo
		$mongo = new MongoClient("localhost:27017");
		$coll = $mongo->selectCollection('hs','orders');
		$coll->drop();
		unset($mongo);
		unset($coll);
// 		// 更新缓存字段
		$this->db->set('isCached',0)->update($table);
	}
	
	
	private function check($table){
		// 1.createTime=0
		$sql= "UPDATE {$table} SET isValid=0,amount=0 WHERE createTime=0 ";
		$this->db->query($sql);
		// 2.获取该表所有用户
		$sql = <<<SQL
		SELECT * FROM
			(SELECT 
				userID,
				GROUP_CONCAT(id) AS idset,
				SUM(amount) - amount AS diffAmount,
				COUNT(id) AS count,
				takeSec
				FROM {$table} 
				WHERE 1=1
				AND createTime>0 
				AND updateTime>{$this->updateTimeLimit}
				AND takeSec>1
				AND amount>1
				GROUP BY userID,createTime,takesec
			)AS t 
		WHERE t.count>1
SQL;
		$query = $this->db->query($sql);
		$list = $query->result_array();
		
		$invalidOrderIdSet = array();
		$total = 0;
		$uidSet = array();
		$userBalanceDiff = array();
		foreach ($list as $row){
			$uid = $row['userID'];
			$user = User::get($uid);
			// 纪录用户被重复扣费金额
			$user->diffAmount+=$row['diffAmount'];
			$uidSet[] = $uid;
			$idSet = explode(',', $row['idset']);
			unset($idSet[0]);//第一张订单不修改
			$invalidOrderIdSet = array_merge($invalidOrderIdSet,$idSet);
			if( count($invalidOrderIdSet)>=100 ){// 每100个更新并重置
				$this->updateInvalidOrders($table, $invalidOrderIdSet);
				$invalidOrderIdSet = array();
			}
		}
		if( ! empty($invalidOrderIdSet) ){
			$this->updateInvalidOrders($table, $invalidOrderIdSet);
			$invalidOrderIdSet = array();
		}
		$this->fetchUserOriginal($uidSet);
		$this->fetchUserStat($uidSet,$table);
	}
	
	private function updateInvalidOrders($table,$invalidOrderIdSet){
		return null;
		$count = count($invalidOrderIdSet);
		$res = $this->db->set('amount',0)
				->set('isValid',0)
				->where_in('id',$invalidOrderIdSet)
				->update($table);
		$affected_rows = $this->db->affected_rows();
		$this->report['updateInvalidOrders'][] = array(
				'res'=>$res,
				'sql'=>$this->db->last_query(),
				'invalidOrderCount'=>$count,
				'affected_rows'=>$affected_rows,
		);
	}
	
	private function fetchUserOriginal($uidSet){
		$list = $this->db->select('id,balance,rechargeTotal,buyTotal,presrent,prepayment,lockedPhoneID')
				->where_in('id',$uidSet)
				->get('hs_user')
				->result_array();
		foreach ($list as $v){
			$user = User::get($v['id']);
			unset($v['id']);
			foreach ($v as $kk=>$vv){
				$user->original->{$kk} = $vv;
			}
		}
	}
	
	private function fetchUserStat($uidSet,$orderTable){
		$uidSet = implode(',', $uidSet);
		$sql = "SELECT u.`id`,rc.`rechargeTotal`,ps.`presrent`,pp.prepayment,buy.buyTotal
				FROM `hs_user` AS u
				LEFT JOIN(
					SELECT userID,SUM(amount) AS buyTotal FROM {$orderTable} WHERE amount>0 GROUP BY userID
				) AS buy ON buy.userID=u.id
				LEFT JOIN( 
					SELECT userID,SUM(amount) AS rechargeTotal FROM `hs_recharge_order` WHERE tradeState='SUCCESS' GROUP BY userID
				) AS rc ON rc.userID=u.`id` 
				LEFT JOIN (
					SELECT userID,SUM(amount) AS presrent FROM `hs_presrent` GROUP BY userID
				)AS ps ON ps.userID=u.id
				LEFT JOIN(
					SELECT userID,SUM(amount) AS prepayment FROM `hs_prepayment_log` GROUP BY userID
				)AS pp ON pp.userID=u.`id`
				WHERE u.id IN ({$uidSet})";
		
		$list = $this->db->query($sql)->result_array();
		foreach ($list as $v){
			$user = User::get($v['id']);
			unset($v['id']);
			foreach ($v as $kk=>$vv){
				$user->stat->{$kk} = $vv;
			}
		}
	}
	
	
	
	
	
	private function updateUserStat($orderTable,$uidSet){
		// 更新该批用户累计消费
		while($set = array_splice($uidSet,0,100)){
			$count = count($set);
			$set = implode(',', $set);
			$sql = <<<SQL
		UPDATE `hs_user` AS u,
		(SELECT userID,SUM(amount) AS total FROM `{$table}` WHERE userID IN({$set}) GROUP BY userID)AS buy
		SET u.buyTotal=buy.total
		WHERE u.id = buy.userID
		AND u.buyTotal-buy.total > 0
		AND u.id IN ({$set})
SQL;
			$res = $this->db->query($sql);
			$affected_rows = $this->db->affected_rows();
				
			$buyTotalReport = array(
					'res'=>$res,
					'sql'=>$sql,
					'count'=>$count,
					'affected_rows'=>$affected_rows
			);
			// 更新该批用户余额
			// 			$sql = " UPDATE `hs_user` SET balance=rechargeTotal+CAST(presrent AS SIGNED)-buyTotal -CAST(prepayment AS SIGNED) ";
			// 			$sql.= " WHERE id IN ({$set})";
			// 			$sql.= " AND id NOT IN(SELECT userID FROM `hs_empty_balance_log`)";
			// 			$res = $this->db->query($sql);
			// 			$affected_rows = $this->db->affected_rows();
			$balanceReport = array(
					'res'=>$res,
					'sql'=>$sql,
					'count'=>$count,
					'affected_rows'=>$affected_rows
						
			);
				
			$this->report['StatUpdate'][] = array(
					'buyTotalReport'=>$buyTotalReport,
					'balanceReport'=>$balanceReport,
			);
				
		}
		// 纪录更新后用户余额
		$this->getUserBalance($table, $_uidSet, 'after');
		
		
		
		
	}

}

class UserBase{
	/**
	 * 累计消费
	 */
	public $buyTotal=0;
	/**
	 * 累计充值
	 */
	public $rechargeTotal=0;
	/**
	 * 累计赠送
	 */
	public $presrent=0;
	/**
	 * 可用余额
	 */
	public $balance=0;
	/**
	 * 预付款余额
	 */
	public $prepayment=0;
	
	public $res = 0;
	
	public function reset(){
		$this->buyTotal  = $this->rechargeTotal + $this->presrent - $this->prepayment - $this->balance;
		
		
		
// 		$this->prepayment = $this->rechargeTotal + $this->presrent - $this->buyTotal - $this->balance;
	}
	
	public function checkRes(){
		$this->res = $this->rechargeTotal + $this->presrent - $this->prepayment - $this->balance - $this->buyTotal;
		
	}
	
}

class User{
	static $_db;
	static $users=array();
	static function get($uid){
		if( ! isset(self::$users[$uid]) ){
			self::$users[$uid] = new self($uid);
		}
		return self::$users[$uid] ;
	}
	static function generate(){
		foreach(self::$users as $user){
			$user->update();
		}
	}
	
	public $id;
	private $db;
	public function __construct($uid){
		$this->db = &self::$_db;
		$this->id= $uid;
		$this->original = new UserBase();
		$this->stat     = new UserBase();
	}
	
	/**
	 * 原始用户数据
	 * @var UserBase
	 */
	public $original;
	
	/**
	 * 统计各个表获取的用户数据
	 * @var UserBase
	 */
	public $stat;
	
	
	
	public $diff=array();
	
	/**
	 * 被多扣的金额
	 */
	public $diffAmount=0;
	
	/**
	 * 预付款表统计出来的预付款余额
	 */
	public $statPrepayment=0;
	
	
	public function reset(){
		$fields = array('buyTotal','rechargeTotal','presrent','prepayment');
		foreach ($fields as $field){
			$this->diff[$field] = $this->original->{$field} - $this->stat->{$field};
		}
		
		$this->stat->balance    = $this->original->balance + $this->diffAmount;
		$this->statPrepayment = $this->stat->prepayment;
		$this->stat->prepayment = $this->original->prepayment;
		$this->stat->reset();
		
		$this->stat->checkRes();
		$this->original->checkRes();
		
	}
	
	public function update(){
		$this->reset();
		$update = get_object_vars($this->stat);
		unset($update['res']);
		$this->db
			->where('id',$this->id)
			->update('hs_user',$update);
		
		
	}
	
	
	
}







